// SQLite 存储服务
import dayjs from "dayjs";
export const StorageService = {
	dbName: 'warehouse',
	isInitialized: false,

	// 检查数据库文件是否存在
	async checkDatabaseFile() {
		return new Promise((resolve) => {
			plus.io.requestFileSystem(plus.io.PRIVATE_DOC, (fs) => {
				fs.root.getFile(
					"warehouse.db", {},
					() => resolve(true),
					() => resolve(false)
				);
			});
		});
	},

	// 初始化数据库
	async init() {
		try {
			// 等待 plus 对象准备就绪
			if (!plus || !plus.sqlite) {
				console.log('SQLite 模块未加载');
				return false;
			}

			// 检查数据库文件是否存在
			// const dbExists = await this.checkDatabaseFile();

			// 先尝试关闭已存在的数据库连接
			plus.sqlite.closeDatabase({
				name: this.dbName
			});
			this.isInitialized = false;


			// 打开数据库
			const openResult = await new Promise((resolve) => {
				plus.sqlite.openDatabase({
					name: this.dbName,
					path: '_doc/warehouse.db',
					success: () => {
						resolve(true);
					},
					fail: (e) => {
						resolve(false);
					}
				});
			});

			if (!openResult) {
				uni.showToast({
					title: '数据库打开失败'
				});

				return false
			}

			// 创建记录表
			const createResult = await new Promise((resolve) => {
				plus.sqlite.executeSql({
					name: this.dbName,
					sql: `
                        CREATE TABLE IF NOT EXISTS records (
                            id TEXT PRIMARY KEY,
                            code TEXT NOT NULL,
                            productName TEXT NOT NULL,
                            specification TEXT,
                            type TEXT NOT NULL,
                            operator TEXT NOT NULL,
                            timestamp INTEGER NOT NULL,
                            synced INTEGER DEFAULT 0
                        )
                    `,
					success: () => {
						resolve(true);
					},
					fail: (e) => {
						resolve(false);
					}
				});
			});

			if (!createResult) {
				uni.showToast({
					title: '记录表创建失败'
				});

				return false
			}

			this.isInitialized = true;

			// 删除旧记录
			// await this.deleteOldRecords();

			return true;
		} catch (error) {
			console.log('数据库初始化失败:', error);
			this.isInitialized = false;
			return false;
		}
	},

	// 检查数据库是否已初始化
	checkInit() {
		if (!this.isInitialized) {
			throw new Error('数据库未初始化');
		}
	},

	// 执行 SQL
	async executeSql(sql) {
		return new Promise((resolve, reject) => {
			this.checkInit();

			// 判断是否是查询语句
			const isQuery = sql.trim().toLowerCase().startsWith('select');
			if (isQuery) {
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(res) {
						console.log(`${sql}查询结果:`, res);
						resolve(res || []);
					},
					fail(e) {
						console.log('查询失败:', e);
						reject(new Error(`查询失败: ${e.message || JSON.stringify(e)}`));
					}
				});
			} else {
				// 使用 executeSql 方法执行其他操作
				plus.sqlite.executeSql({
					name: this.dbName,
					sql: sql,
					success(res) {
						console.log(res);
						resolve(res);
					},
					fail(e) {
						console.log('SQL执行失败:', e);
						reject(new Error(`SQL执行失败: ${e.message || JSON.stringify(e)}`));
					}
				});
			}
		});
	},

	// 保存扫码记录
	async saveRecord(record) {
		try {
			this.checkInit();
			const operator = '默认用户';

			// 构建 SQL 语句
			const sql = `
			    INSERT INTO records (id, code, productName, specification, type, operator, timestamp, synced) 
			    VALUES ('${record.id.trim()}', '${record.code.trim()}', '${record.productName.trim()}', 
			    '${(record.specification || '').trim()}', '${record.type.trim()}', 
			    '${operator.trim()}', ${record.timestamp}, 0)
			`;

			console.log('保存扫码记录SQL:', sql);

			// 执行插入
			await this.executeSql(sql);
			return {
				success: true,
				record: {
					...record,
					operator,
					synced: false
				}
			};
		} catch (error) {
			uni.showToast({
				title: `保存失败: ${error.message || '未知错误'}`,
				icon: 'none',
				duration: 3000
			});
			return {
				success: false
			};
		}
	},

	// 获取所有记录
	async getRecords() {
		try {
			this.checkInit();
			const result = await this.executeSql('SELECT * FROM records ORDER BY timestamp DESC');
			// console.log('获取所有扫码记录:', result);

			return result.map(row => ({
				...row,
				synced: row.synced === 1
			}));
		} catch (error) {
			console.log('获取记录失败:', error);
			return [];
		}
	},

	// 获取待同步记录
	async getPendingRecords() {
		try {
			this.checkInit();
			const todayStart = dayjs().startOf('day').valueOf();
			const todayEnd = dayjs().endOf('day').valueOf();
			const result = await this.executeSql(
				`SELECT * FROM records WHERE synced = 0 AND timestamp BETWEEN ${todayStart} AND ${todayEnd}`
			);

			// 确保返回的是数组
			const records = Array.isArray(result) ? result :
				(result && result.rows) ? Array.from(result.rows) : [];

			return records.map(row => ({
				...row,
				synced: false
			}));
		} catch (error) {
			console.log('获取待同步记录失败:', error);
			return [];
		}
	},

	// 标记记录为已同步
	async markAsSynced(recordIds) {
		try {
			this.checkInit();
			// 构建 IN 子句的值列表
			const idList = recordIds.map(id => `'${id}'`).join(',');
			const sql = `UPDATE records SET synced = 1 WHERE id IN (${idList})`;

			console.log('执行同步SQL:', sql);

			// 执行更新
			const result = await this.executeSql(sql);
			console.log('同步结果:', JSON.stringify(result));

			// 验证更新是否成功
			const updatedRecords = await this.executeSql(
				`SELECT COUNT(*) as count FROM records WHERE id IN (${idList}) AND synced = 1`
			);
			console.log('验证结果:', JSON.stringify(updatedRecords));

			// 检查更新数量是否正确
			const updateCount = updatedRecords[0]?.count || 0;
			const success = updateCount === recordIds.length;

			if (!success) {
				console.error('同步记录数量不匹配:', {
					expected: recordIds.length,
					actual: updateCount
				});
				uni.showModal({
					title: '同步记录数量不匹配',
					content: error
				});
			}

			return success;
		} catch (error) {
			uni.showModal({
				title: '标记同步状态失败',
				content: error
			});
			return false;
		}
	},

	// 关闭数据库
	closeDatabase() {
		if (this.isInitialized) {
			plus.sqlite.closeDatabase({
				name: this.dbName
			});
			this.isInitialized = false;
		}
	},
	// 删除旧记录
	async deleteOldRecords() {
		try {
			const todayStart = dayjs().startOf('day').valueOf();
			// const sql = `DELETE FROM records WHERE timestamp < ${todayStart}`;
			const sql = `DELETE FROM records`;
			const result = await this.executeSql(sql);
			uni.showToast({
				title: '数据已清空'
			})

		} catch (error) {
			return uni.showToast({
				title: '删除旧记录失败' + JSON.stringify(error),
			});

		}
	}
};